USE Lib
GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
----------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp_data_compare') BEGIN
	DROP PROCEDURE tsql.sp_data_compare
	PRINT '<<< DROP PROCEDURE tsql.sp_data_compare - Completed with SUCCESS >>>'
END

GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*================================================================================================
Name:			tsql.sp_data_compare
Type:			User defined procedure 
Owner:					
Description:	Compare the data between 2 tables/views and returns the diffs
Parameters:		@ref_db = Optional, Db name where ref table/view resides, if ommited current database is assumed
				@ref_schema = Optional, schema name where ref table/view resides, if omitted DBO is used
				@ref_table = name of the table/view to use as reference, allows SQL statements 
				@ref_return_columns = Optional, comma separated list of columns to be returned from ref table after compare, use * for all colls 
				@cmp_db = Optional, compare db name, if ommited @ref_db nasme is used
				@cmp_schema = Optional, compare schema name, if omited DBO is used 
				@cmp_table = Name of the table/view to compare, SQL statement allowed  
				@cmp_return_columns = Optional, comma separated list of columns to be returned from compare table, use * for all cols  
				@join_keys = Comma separated list of columns to join the 2 tables  
				@compare_columns = Comma separated list of columns to use to determine changes
				@options = Indicates the level of comparission to be performed:
							0 - return all information including matching records
							1 - return only differences	 
				@debug = 1 displays internal variables only 

Dependens on:  	none
Usage:			
--------------------------------------------------------------------------------------------------
Project:		http://sqllib.codeplex.com/
Help:			http://sqllib.codeplex.com/wikipage?title=sp_data_compare&referringTitle=Documentation		
Version:		201208221
SrcCtrlApp:		SVN
SrcCtrlPath:	http://sqllib.codeplex.com/SourceControl/list/changesets
Note*:			CTRL + click on the link above to load the page
--------------------------------------------------------------------------------------------------
History:		10/21/2005 - Adrian E Dudau - Procedure created
History:		08/22/2012 - Adrian E Dudau - Added references to project, SVN and Help
================================================================================================*/

CREATE PROCEDURE tsql.sp_data_compare
(
  @ref_db AS SYSNAME = NULL
, @ref_schema AS SYSNAME = 'dbo'
, @ref_table AS NVARCHAR(4000)
, @ref_return_columns AS NVARCHAR(4000) = '*'
, @cmp_db AS SYSNAME = NULL
, @cmp_schema AS SYSNAME = 'dbo'
, @cmp_table AS NVARCHAR(4000) = ''  
, @cmp_return_columns AS NVARCHAR(4000) = '*'
, @join_keys AS NVARCHAR(4000) 
, @compare_columns AS NVARCHAR(4000) = ''
, @options AS INT = 0 
, @debug BIT = 0 
) 

AS

BEGIN /*PROCEDURE*/
	DECLARE @SQL VARCHAR(8000) 
	DECLARE @Buffer AS VARCHAR(4000)
	DECLARE @UpdFlds AS VARCHAR(4000)	
	DECLARE @Result VARCHAR(4000)
	
	DECLARE @CurrPosQ INT
	DECLARE @CurrPosP INT
	DECLARE @LastPosP INT


	SET @SQL = '' 
	SET @Buffer = ''
	SET @UpdFlds = ''
	SET @Result = ''
	SET @CurrPosQ = 1
	SET @CurrPosP = 1
	SET @LastPosP = 1

-- Check Input parameters
	IF LEN(ISNULL(@ref_db,'')) = 0 
		SET @ref_db = DB_NAME(); 	

	IF LEN(ISNULL(@cmp_db,'')) = 0 
		SET @cmp_db = @ref_db; 	

	IF LEN(ISNULL(@ref_schema,'')) = 0 
		SET @ref_schema = 'dbo';
		
	IF LEN(ISNULL(@cmp_schema,'')) = 0 
		SET @cmp_schema = 'dbo';

	IF LEN(ISNULL(@ref_table,'')) = 0 BEGIN 
		RAISERROR ( 'Please specify reference table name!', 16, 1)
		RETURN  
	END	 

	IF LEN(ISNULL(@cmp_table,'')) = 0 BEGIN 
		RAISERROR ( 'Please specify compare table name!', 16, 1)
		RETURN  
	END	 


-- Qualify table names with db and schema name
	SET @ref_table = REPLACE(REPLACE('[' + @ref_db + '].[' + @ref_schema + '].[' + @ref_table + ']','[[','['),']]',']');
	SET @cmp_table = REPLACE(REPLACE('[' + @cmp_db + '].[' + @cmp_schema + '].[' + @cmp_table + ']','[[','['),']]',']'); 

	IF LEN(@ref_return_columns) > 0 BEGIN 
		SET @ref_return_columns = REPLACE(@ref_return_columns,SPACE(1),'')
		SET @ref_return_columns = 'ref.[' + REPLACE(@ref_return_columns,',','ref.[') + ']'
		SET @ref_return_columns = REPLACE(@ref_return_columns,'[*]','*') 
	END

	IF LEN(@cmp_return_columns) > 0  BEGIN 
		SET @cmp_return_columns = REPLACE(@cmp_return_columns,SPACE(1),'')
		SET @cmp_return_columns = 'cmp.[' + REPLACE(@cmp_return_columns,',',', cmp.[') + ']'
		SET @cmp_return_columns = REPLACE(@cmp_return_columns,'[*]','*') 
	END

	SET @join_keys = REPLACE(@join_keys,SPACE(1),'')

	IF RIGHT(@join_keys, 1) <> ',' 
		SET @join_keys = @join_keys + ','
       
	WHILE @CurrPosP > 0 BEGIN
		SET @CurrPosP = CHARINDEX(',', @join_keys, @LastPosP)

		IF @CurrPosP > 0 BEGIN
			SET @Buffer = SUBSTRING(@join_keys, @LastPosP, @CurrPosP - @LastPosP)
		    
			IF LEN(@Buffer) > 0 BEGIN 
				SET @CurrPosQ = CHARINDEX('=', @Buffer, 1)
				SET @Result = @Result + 'ref.[' + LEFT(@Buffer, @CurrPosQ - 1) + '] = cmp.[' + SUBSTRING(@Buffer, @CurrPosQ + 1,1000) + ']'
		        
		        IF LEN(SUBSTRING(@join_keys,@CurrPosP, 1000)) > 0 
					SET @Result = @Result + ' AND '
		            
			END /*IF LEN(@Buffer) > 0*/

			SET @Buffer = ''
			SET @LastPosP = @CurrPosP + 1
		END /*IF @CurrPosP > 0*/
	END /*WHILE @CurrPosP > 0*/
	
	IF RIGHT(LTRIM(RTRIM(@Result)), 3) = 'AND' 
		SET @Result = LEFT(@Result, LEN(@Result) - 4)
	
	SET @join_keys = @Result 

	SET @Buffer = ''
	SET @Result = ''

	SET @CurrPosQ = 1
	SET @CurrPosP = 1
	SET @LastPosP = 1

	SET @compare_columns = REPLACE( @compare_columns,SPACE(1),'')

	IF RIGHT(@compare_columns, 1) <> ',' 
		SET @compare_columns = @compare_columns + ','
       
	WHILE @CurrPosP > 0 BEGIN
		SET @CurrPosP = CHARINDEX(',', @compare_columns, @LastPosP)

		IF @CurrPosP > 0 BEGIN
			SET @Buffer = SUBSTRING(@compare_columns, @LastPosP, @CurrPosP - @LastPosP)
		    
			IF LEN(@Buffer) > 0 BEGIN 
				SET @CurrPosQ = CHARINDEX('=', @Buffer, 1)
				SET @Result = @Result + 'ref.[' + LEFT(@Buffer, @CurrPosQ - 1) + '] <> cmp.[' + SUBSTRING(@Buffer, @CurrPosQ + 1,1000) + ']'
		        SET @UpdFlds = @UpdFlds + ' + CASE WHEN (' + 'ref.[' + LEFT(@Buffer, @CurrPosQ - 1) + '] <> cmp.[' + SUBSTRING(@Buffer, @CurrPosQ + 1,1000) + ']) THEN ''' + 'ref.' + LEFT(@Buffer, @CurrPosQ - 1) + ' <> cmp.' + SUBSTRING(@Buffer, @CurrPosQ + 1,1000) + ';'' ELSE '''' END' 
		        
		        IF LEN(SUBSTRING(@compare_columns,@CurrPosP, 1000)) > 0 
					SET @Result = @Result + ' AND '
		            
			END /*IF LEN(@Buffer) > 0*/ 

			SET @Buffer = ''
			SET @LastPosP = @CurrPosP + 1
		END /*IF @CurrPosP > 0 */
	END /*WHILE @CurrPosP > 0*/
	

	IF RIGHT(RTRIM(@Result), 3) = 'AND' 
		SET @Result = LEFT(@Result, LEN(@Result) - 4)

	SET @compare_columns = @Result 

-- DYNAMIC SQL STATEMENT 
	SET @SQL = @SQL + ' SELECT diff_type = CASE WHEN REF.id IS NULL THEN ''I'' '
	SET @SQL = @SQL + ' WHEN CMP.id IS NULL THEN ''D'' '
	SET @SQL = @SQL + ' WHEN (' + REPLACE(@compare_columns,'AND','OR') + ') THEN ''U'' '
	SET @SQL = @SQL + '	ELSE '''''
	SET @SQL = @SQL + '	END '
	SET @SQL = @SQL + ', diff_descr = CASE WHEN REF.id IS NULL THEN ''New row in cmp'''
	SET @SQL = @SQL + '	WHEN CMP.id IS NULL THEN ''Deleted row in cmp'''
	SET @SQL = @SQL + ' WHEN (' + REPLACE(@compare_columns,'AND','OR') + ') THEN '''''
	SET @SQL = @SQL + '	' + @UpdFlds 		
	SET @SQL = @SQL + '	ELSE '''''
	SET @SQL = @SQL + '	END '
	SET @SQL = @SQL + '	,'
-- Dynamic Fields list
	IF LEN(@ref_return_columns) > 0  
		SET @SQL = @SQL + @ref_return_columns 

	IF LEN(@cmp_return_columns) > 0 BEGIN
		IF LEN(@ref_return_columns) > 0  
			SET @SQL = @SQL + ', ' + @cmp_return_columns 
		ELSE 			
			SET @SQL = @SQL + @cmp_return_columns 

	END /*LEN(@cmp_return_columns) > 0*/
-- Dynamic Fields list
	IF PATINDEX('%SELECT%',@ref_table) = 0 
		SET @SQL = @SQL + ' FROM (SELECT id = 1, * FROM ' + @ref_table + ') REF ' 
	ELSE
		SET @SQL = @SQL + ' FROM (SELECT id = 1, * FROM (' + @ref_table + ')) REF ' 

-- DYNAMIC FILTER
	IF LEN(@join_keys) > 0 
		SET @SQL = @SQL + ' FULL OUTER JOIN  '  
	ELSE 
		SET @SQL = @SQL + ' CROSS JOIN  '  

-- DYNAMIC FILTER

	IF PATINDEX('%SELECT%',@cmp_table) = 0 
		SET @SQL = @SQL + ' (SELECT id = 1, * FROM ' + @cmp_table + ') CMP ' 
	ELSE
		SET @SQL = @SQL + ' (SELECT id = 1, * FROM (' + @cmp_table + ')) CMP ' 

	IF LEN(@join_keys) > 0 
		SET @SQL = @SQL + ' ON ' + @join_keys  

	IF @options = 1 BEGIN 
		SET @SQL = @SQL + ' WHERE  (' + REPLACE(REPLACE(REPLACE(@join_keys,'=','IS NULL OR'),'AND','OR IS NULL'),'OR IS NULL','IS NULL OR') + ' IS NULL) ';

		IF LEN(@compare_columns) > 0 BEGIN
			IF @options = 1 
				SET @SQL = @SQL + ' OR (' + REPLACE(@compare_columns,'AND','OR')  + ')' 
			ELSE
				SET @SQL = @SQL + ' WHERE (' + REPLACE(@compare_columns,'AND','OR')  + ')' 
		END /*LEN(@compare_columns) > 0*/
	END
		

	IF @debug = 1 
	BEGIN 
		PRINT '@ref_db(IN) = ' + ISNULL(@ref_db, '')
		PRINT '@ref_table(IN) = ' + ISNULL(@ref_table,'')
		PRINT '@ref_return_columns(IN) = ' + ISNULL(@ref_return_columns,'')
		PRINT '@cmp_db(IN) = ' + ISNULL(@cmp_db,'')
		PRINT '@cmp_table(IN) = ' + ISNULL(@cmp_table,'')
		PRINT '@cmp_return_columns(IN) = ' + ISNULL(@cmp_return_columns,'')
		PRINT '@join_keys(IN) = ' + ISNULL(@join_keys,'')
		PRINT '@compare_columns(IN) = ' + ISNULL(@compare_columns,'')
		PRINT '(optional IN params) ---------------------------------------------------------------------' 
		PRINT '@options(IN) = ' + CAST(ISNULL(@options,0) AS VARCHAR)
		PRINT '@debug(IN) = ' + CAST(ISNULL(@debug,0) AS VARCHAR) 
		PRINT '(Internal Variables) ------------------------------------------------------------------------' 
		PRINT '@SQL = ' + ISNULL(@SQL,'')
		PRINT '@Buffer = ' + ISNULL(@Buffer,'')
		PRINT '@Result = ' + ISNULL(@Result,'')
		PRINT '@CurrPosQ = ' + CAST(@CurrPosQ AS VARCHAR)
		PRINT '@CurrPosP = ' + CAST(@CurrPosP AS VARCHAR)
		PRINT '@LastPosP = ' + CAST(@LastPosP AS VARCHAR) 
	END
	ELSE
		EXEC (@SQL)

END /*PROCEDURE*/

GO

---------------------------------------------------------------------------
-- BEGIN MAINTENANCE WRAPPER
-- Verify completion
----------------------------
IF EXISTS(SELECT * FROM sysobjects WHERE name = 'sp_data_compare') BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_data_compare - Completed with SUCCESS >>>'
END

ELSE BEGIN 
	PRINT '<<< CREATE PROCEDURE tsql.sp_data_compare - Completed with ERROR >>>'
END


GO
----------------------------
-- END MAINTENANCE WRAPPER
---------------------------------------------------------------------------

--  GRANT SELECT TO PUBLIC will be used at Db level
